#!/bin/bash

APP=gmall

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d "-1 day" +%F`
fi

common_sql="use ${APP};set hive.exec.dynamic.partition.mode=nonstrict;"

dws_trade_user_sku_order_nd="
    insert overwrite table dws_trade_user_sku_order_nd partition (dt = '${do_date}')
    select
        user_id,
        sku_id,
        sku_name,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        tm_id,
        tm_name,
        sum(if(dt >= date_sub('${do_date}', 6), order_count_1d, 0)) order_count_7d,
        sum(if(dt >= date_sub('${do_date}', 6), order_num_1d, 0)) order_num_7d,
        sum(if(dt >= date_sub('${do_date}', 6), order_original_amount_1d, 0)) order_original_amount_7d,
        sum(if(dt >= date_sub('${do_date}', 6), activity_reduce_amount_1d, 0)) activity_reduce_amount_7d,
        sum(if(dt >= date_sub('${do_date}', 6), coupon_reduce_amount_1d, 0)) coupon_reduce_amount_7d,
        sum(if(dt >= date_sub('${do_date}', 6), order_total_amount_1d, 0)) order_total_amount_7d,
        sum(order_count_1d) order_count_30d,
        sum(order_num_1d) order_num_30d,
        sum(order_original_amount_1d) order_original_amount_30d,
        sum(activity_reduce_amount_1d) activity_reduce_amount_30d,
        sum(coupon_reduce_amount_1d) coupon_reduce_amount_30d,
        sum(order_total_amount_1d) order_total_amount_30d
    from dws_trade_user_sku_order_1d
    where dt >= date_sub('${do_date}', 29) and dt <= '${do_date}'
    group by
        user_id,
        sku_id,
        sku_name,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        tm_id,
        tm_name;
"

dws_trade_province_order_nd="
    insert overwrite table dws_trade_province_order_nd partition (dt = '2020-06-15')
    select
        province_id,
        province_name,
        area_code,
        iso_code,
        iso_3166_2,
        sum(if(dt >= date_sub('${do_date}', 6), order_count_1d, 0)) order_count_7d,
        sum(if(dt >= date_sub('${do_date}', 6), order_num_1d, 0)) order_num_7d,
        sum(if(dt >= date_sub('${do_date}', 6), order_original_amount_1d, 0)) order_original_amount_7d,
        sum(if(dt >= date_sub('${do_date}', 6), activity_reduce_amount_1d, 0)) activity_reduce_amount_7d,
        sum(if(dt >= date_sub('${do_date}', 6), coupon_reduce_amount_1d, 0)) coupon_reduce_amount_7d,
        sum(if(dt >= date_sub('${do_date}', 6), order_total_amount_1d, 0)) order_total_amount_7d,
        sum(order_count_1d) order_count_30d,
        sum(order_num_1d) order_num_30d,
        sum(order_original_amount_1d) order_original_amount_30d,
        sum(activity_reduce_amount_1d) activity_reduce_amount_30d,
        sum(coupon_reduce_amount_1d) coupon_reduce_amount_30d,
        sum(order_total_amount_1d) order_total_amount_30d
    from dws_trade_province_order_1d
    where dt >= date_sub('${do_date}', 29) and dt <= '${do_date}'
    group by
        province_id,
        province_name,
        area_code,
        iso_code,
        iso_3166_2;
"

# 判断表名参数，表名参数是第一个参数
case $1 in
"dws_trade_user_sku_order_nd")
    hive -e "${common_sql}${dws_trade_user_sku_order_nd}"
;;
"dws_trade_province_order_nd")
    hive -e "${common_sql}${dws_trade_province_order_nd}"
;;
"all")
    hive -e "${common_sql}${dws_trade_user_sku_order_nd}${dws_trade_province_order_nd}"
;;
esac